package com.cn.ccp.service.system;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.SelectedCourse;
import com.cn.ccp.bean.UserBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.MsgConstants;

public class StudentService extends BasicService {
	
	private Log log = LogFactory.getLog(getClass());
	
	public int sava(final UserBean userBean) {
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					
					String filePathSql = "SELECT  id FROM file_path WHERE flag = TRUE ";
					
					int fpath = -1 ;
					try {
						fpath = (Integer) jdbcTemplate.queryForObject(filePathSql, Integer.class);
					} catch (Exception e) {
						// TODO: handle exception
					}
					final int filePath = fpath ;
					
					int tmpid = CCPContants.getClobalIndex();
					String year = "" ;
					try {
						year = ""+(Integer.parseInt(CCPContants.sdf.format(CCPContants.sdf.parse(userBean.getAdmissionTime())))+ CCPContants.undergraduate_year );
					} catch (Exception e) {
						e.printStackTrace();
						log.error("sava user  AdmissionTime failure");
						CCPContants.tranmitResultMap.put(index, false);
						return null; 
					} 
					String faculty = ""+CCPContants.valueTransitionTo2(Integer.parseInt(userBean.getFacultyId()));
					String specialty = "";
					String tmp = userBean.getSpecialtyId() ;
					if(tmp != null && !tmp.equals("")){
						specialty = tmp.split(":")[0];
					}
					specialty = CCPContants.valueTransitionTo2(Integer.valueOf(specialty));
					userBean.setNumber(""+tmpid);
					String passwordSql = "select password(?)";
					String md5Password = (String) jdbcTemplate.queryForObject(passwordSql, new Object[]{userBean.getPassword()} ,String.class);
					
					userBean.setPassword(md5Password);
					final String sql = "INSERT INTO user ( number, username, PASSWORD, dormitory_address, email, sex,mobile,user_type, home_address, birthdate, zip_code, admission_time,faculty_id, specialty_id, enrollment, image, grade, STATUS ,loginname,file_path_id)VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					KeyHolder keyHolder = new GeneratedKeyHolder();  
					jdbcTemplate.update(  
							   new PreparedStatementCreator() {  
							    public PreparedStatement createPreparedStatement(Connection con) throws SQLException   
							    {  
								     	PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
								     	ps.setString(1, userBean.getNumber());
										ps.setString(2, userBean.getUserName());
										ps.setString(3, userBean.getPassword());
										ps.setString(4, userBean.getDormitoryAddress());
										ps.setString(5, userBean.getEmail());
										ps.setString(6, userBean.getSex());
										ps.setString(7, userBean.getMobile());
										ps.setString(8, userBean.getUserType() );
										ps.setString(9, userBean.getHomeAddress());
										ps.setString(10, userBean.getBirtdate());
										ps.setString(11, userBean.getZipCode());
										ps.setString(12, userBean.getAdmissionTime());
										ps.setString(13, userBean.getFacultyId());
										ps.setString(14, userBean.getSpecialtyId());
										ps.setFloat(15, userBean.getEnrollment());
										ps.setString(16, userBean.getImage());
										ps.setString(17, userBean.getGrade());
										ps.setInt(18, userBean.getStatus());
										ps.setString(19, userBean.getLogin_name());
										ps.setInt(20, filePath);
										
								     return ps;  
							    }  
							   }, keyHolder);  
					int userId = keyHolder.getKey().intValue();
					
				 
					int count = jdbcTemplate.queryForInt("select count(id) from user where grade=? and specialty_id=? and user_type = 1 and year(admission_time)=? "  , new Object[]{userBean.getGrade(),userBean.getSpecialtyId() ,userBean.getAdmissionTime().substring(0, 4)});
					String studentIndex = CCPContants.valueTransitionTo4(count);
					String studentNumber = year + faculty + specialty + studentIndex ;
					jdbcTemplate.update("UPDATE USER SET number = ? WHERE id = ?", new Object[]{studentNumber, userId});
									
					//赋予默认角色
					jdbcTemplate.update("INSERT INTO role_user (user_id ,role_id) VALUES ( ?, ?)" ,new Object[]{userId , 2});
					
					CCPContants.tranmitResultMap.put(index, userId);
				} catch (Exception e) {
					arg0.setRollbackOnly();
					e.printStackTrace();
					CCPContants.tranmitResultMap.put(index, -1);
				}
				return null;
			}
		});
		return (Integer) CCPContants.tranmitResultMap.get(index) ;
		
	}

	public boolean update(Object entity ,boolean isChangePassword) {
		UserBean userBean = (UserBean) entity;
		log.info(userBean);
		String oldImage = findById(userBean.getId()).getImage();
		if(!oldImage.equalsIgnoreCase(userBean.getImage())){
			File file = new File(CCPContants.SYS_PATH+File.separator+oldImage);
			if(file.exists()) file.delete(); 
		}
		
		 if(isChangePassword){
		 	String passwordSql = "select password(?)";
			String md5Password = (String) jdbcTemplate.queryForObject(passwordSql, new Object[]{userBean.getPassword()} ,String.class);
			
			userBean.setPassword(md5Password);
			String sql = "update  user set username=?, PASSWORD=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, STATUS=? ,loginname = ? where id = ?";
			return jdbcTemplate.update(sql ,new Object[]{userBean.getUserName(),userBean.getPassword(),
					userBean.getDormitoryAddress(),userBean.getEmail(),
					userBean.getSex(),userBean.getMobile(),
					userBean.getUserType() ,userBean.getHomeAddress(),
					userBean.getBirtdate(),userBean.getZipCode(),
					userBean.getAdmissionTime(),userBean.getFacultyId(),
					userBean.getSpecialtyId(),userBean.getEnrollment(),
					userBean.getImage(),userBean.getGrade(),
					userBean.getStatus(),userBean.getLogin_name() ,userBean.getId()}) > 0;
		}else{
			String sql = "update  user set username=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, STATUS=? ,loginname = ?where id = ?";
			return jdbcTemplate.update(sql ,new Object[]{userBean.getUserName(),
					userBean.getDormitoryAddress(),userBean.getEmail(),
					userBean.getSex(),userBean.getMobile(),
					userBean.getUserType() ,userBean.getHomeAddress(),
					userBean.getBirtdate(),userBean.getZipCode(),
					userBean.getAdmissionTime(),userBean.getFacultyId(),
					userBean.getSpecialtyId(),userBean.getEnrollment(),
					userBean.getImage(),userBean.getGrade(),
					userBean.getStatus(),userBean.getLogin_name() ,userBean.getId()}) > 0;
			
		}
	}

	public boolean delete(int id) {
		String sql = "update user set status = -1 where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}
	
	public void deleteStudent(final int userId){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					//删除role_user表
					String sql = "delete from role_user where user_id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
					
					sql = "delete from student_marked_history where student_id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
					//删除selected_course_detail表
					sql = "delete from selected_course_detail where selected_course_id in (select id from selected_course where student_id = ?)";
					jdbcTemplate.update(sql ,new Object[]{userId});
					//删除 selected_course 表
					sql = "delete from selected_course where student_id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
					//删除用户表
					 sql = "delete from user where id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("delete student["+userId+"] failed :", e);
				}
				return null;
			}
		});
	}
	
	
	

	public UserBean findById(int id) {
		String sql = "SELECT u.*   FROM USER u  where u.id = ? " ;
		return (UserBean) jdbcTemplate.queryForObject(sql, new Object[] { id }, new UsersMapper());
	}

	public int getCount( String number ,String  searchUserType ,String  searchStatus,String  searchLoginUserName,String searchFacultyId ,String  searchSpecialtyId) {
		String sql = "select count(1) from USER u ";
		StringBuffer sb = new StringBuffer() ;
		if(number != null  && !number.equals(""))
			sb.append(" u.number = '"+number+"' ");
		if(searchUserType != null  && !searchUserType.equals("") && !searchUserType.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.user_type = "+searchUserType);
		}
		if(searchStatus != null  && !searchStatus.equals("") && !searchStatus.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.status = "+searchStatus);
		}
		if(searchLoginUserName != null  && !searchLoginUserName.equals("") ){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.username like '%"+searchLoginUserName+"%' ");
		}
		if(searchFacultyId != null  && !searchFacultyId.equals("") && !searchFacultyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.faculty_id = "+searchFacultyId);
		}
		if(searchSpecialtyId != null  && !searchSpecialtyId.equals("") && !searchSpecialtyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" (u.specialty_id = "+searchSpecialtyId + " or u.specialty_id lik '%:"+searchSpecialtyId+"%' or u.specialty_id lik '%"+searchSpecialtyId+":%')");
		}
		sql = sql + "  where u.user_type = 1 and u.status != -1 ";
		if(sb.length() > 0)
			sql = sql + " and " + sb.toString() ;
		try {
			return jdbcTemplate.queryForInt(sql);
		} catch (Exception e) {
			return 0;
		}
	}
	public List pageQuery(int start, int pagesize , String number ,String  searchUserType ,String  searchStatus,String  searchLoginUserName,String searchFacultyId ,String  searchSpecialtyId) {
		String sql = "SELECT u.*  FROM USER u ";
		
		StringBuffer sb = new StringBuffer() ;
		if(number != null  && !number.equals(""))
			sb.append(" u.number = '"+number+"' ");
		if(searchUserType != null  && !searchUserType.equals("") && !searchUserType.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.user_type = "+searchUserType);
		}
		if(searchStatus != null  && !searchStatus.equals("") && !searchStatus.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.status = "+searchStatus);
		}
		if(searchLoginUserName != null  && !searchLoginUserName.equals("") ){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.username like '%"+searchLoginUserName+"%' ");
		}
		if(searchFacultyId != null  && !searchFacultyId.equals("") && !searchFacultyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.faculty_id = "+searchFacultyId);
		}
		if(searchSpecialtyId != null  && !searchSpecialtyId.equals("") && !searchSpecialtyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" (u.specialty_id = "+searchSpecialtyId + " or u.specialty_id lik '%:"+searchSpecialtyId+"%' or u.specialty_id lik '%"+searchSpecialtyId+":%')");
		}
		sql = sql + " where u.user_type = 1 and u.status != -1 " ;
		if(sb.length()> 0)
			sql = sql +" and "+ sb.toString() ;
		sql = sql  +" limit ?,?" ;
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new UsersMapper());
	}
	
	
	public List<UserBean> getSelectCourseStudentList (String courseid){
		String sql = "SELECT u.* ,sc.score FROM selected_course sc "+
					" LEFT JOIN USER u ON u.id = sc.student_id "+
					" WHERE sc.course_id = ? AND sc.status != -1";
		return jdbcTemplate.query(sql, new Object[]{courseid}, new UsersMapper());
	}
	
	public SelectedCourse getSelectedCourseByStuAndCourseId(String stuId,String courseId){
		String sql = "select sc.score from selected_course sc where sc.student_id=? and sc.course_id = ?";
		return (SelectedCourse)jdbcTemplate.queryForObject(sql, new Object[]{stuId,courseId}, new SelectedCourseMapper());
	}
	
	
	
	public List<UserBean> getStudentListByGradeId(String gradeid,String courseId){
		String sql = "SELECT u.* ,cm.id as cmid FROM USER u "+
					" LEFT OUTER JOIN course_mapping cm ON cm.other_id = u.id AND cm.course_mapping_type = 1 "+
					" and cm.course_id = ? WHERE u.grade = ?";
		return jdbcTemplate.query(sql, new Object[]{courseId,gradeid} , new UsersMapper());
	}
	
	class SelectedCourseMapper implements RowMapper{

		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			SelectedCourse bean = new SelectedCourse();
			bean.setScore(rs.getInt("score"));
			return bean;
		}
		
	}
	
	
	public class UsersMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			UserBean userBean = new UserBean();
			userBean.setId(rs.getInt("id"));
			userBean.setNumber(rs.getString("number"));
			userBean.setLogin_name(rs.getString("loginname"));
			userBean.setUserName(rs.getString("username"));
			String password = rs.getString("password") ;
			userBean.setShowPassword(password);
			StringBuffer sb = new StringBuffer();
			for (int j = 0; j < password.length(); j++) 
				sb.append("*");
			userBean.setPassword(sb.toString());
			userBean.setDormitoryAddress(rs.getString("dormitory_address"));
			userBean.setEmail(rs.getString("email"));
			userBean.setSex(rs.getString("sex"));
			try{
				if(userBean.getSex().equals("1")){
					userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_MAN));
				}else if(userBean.getSex().equals("0")){
					userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_WOMAN));
				}
			
				userBean.setMobile(rs.getString("mobile"));
				userBean.setUserType(rs.getString("user_type"));
				if(userBean.getUserType().equals("1")){
					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_STUDENT));
				}else if(userBean.getUserType().equals("2")){
					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_FULL_TIME_TEACHER));
				}else if(userBean.getUserType().equals("3")){
					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_PART_TIME_TEACHER));
				}else if(userBean.getUserType().equals("4")){
					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_EXT_TEACHER));
				}
			}catch(Exception e){}
			userBean.setHomeAddress(rs.getString("home_address"));
			userBean.setBirtdate(rs.getString("birthdate"));
			if(userBean.getBirtdate() != null  && !userBean.getBirtdate().equals(""))
				userBean.setBirtdate(userBean.getBirtdate().substring(0,10));
			userBean.setZipCode(rs.getString("zip_code"));
			userBean.setAdmissionTime(rs.getString("admission_time"));
			if(userBean.getAdmissionTime() != null  && !userBean.getAdmissionTime().equals(""))
				userBean.setAdmissionTime(userBean.getAdmissionTime().substring(0,10));
			userBean.setFacultyId(rs.getString("faculty_id"));
			userBean.setSpecialtyId(rs.getString("specialty_id"));
			userBean.setEnrollment(rs.getFloat("enrollment"));
			userBean.setImage(rs.getString("image"));
			userBean.setGrade(rs.getString("grade"));
			userBean.setStatus(rs.getInt("status"));
			try{
				if(userBean.getStatus() == 0){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_OK));
				}else if(userBean.getStatus() == -1){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_DELETE));
				}else if(userBean.getStatus() == -2){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_NO));
				}
				String facultyname = BaseAction.getInstance().getFacultyService().findFacultyById(Integer.parseInt(userBean.getFacultyId())).getFacultyName();
				userBean.setFaculty(facultyname);
			}catch(Exception e){}
			try {
				String cmid = rs.getString("cmId");
				if(cmid != null  && !cmid.equals(""))
					userBean.setReleaseSelect(true);
			} catch (Exception e) {
				// TODO: handle exception
			}
			userBean.setFile_path_id(rs.getInt("file_path_id"));
			return userBean;
		}
	}
}
